# -*- coding: utf-8 -*-
import os
import xlsxwriter
import numpy as np
import skin_resistor.line as lineParser
from skin_resistor.info import skinInfoType
import skin_resistor as skinRes
from skin_resistor.usrInfo import skinUsrEnum
import skin_resistor.file as sfile 
from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtWidgets import *
from PyQt5.QtCore import *
from PyQt5.QtGui import *

def excelColumnString(pos):
    sList = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 
             'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R',
             'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z']

    """10进制 -> 26进制"""
    mod, remainder = divmod(pos, 26)
    alphabet = sList[remainder]

    # print(pos, mod, remainder, alphabet)

    while mod:
        mod, remainder = divmod(mod-1, 26)
        alphabet = sList[remainder] + alphabet
        # print(pos, mod, remainder, alphabet)

    return alphabet

def excelTableString(pos, row):
    return excelColumnString(pos) + str(row)

def excelSelectAreaString(startPos, startRow, endPos, endRow):
    return excelColumnString(startPos) + str(startRow) + ':' + excelColumnString(endPos) + str(endRow)

def writeSkinReportHeadToExcel(sheet, startLine, usrInfo, format):
    usrInfoLen = usrInfo.getLen()

    if usrInfoLen == 0:
        return startLine

    line = startLine
    endLine = startLine

    infoList = usrInfo.infoList()

    for info in infoList:
        line = startLine + info.pos

        if info.isNeedMerge == 1:
            mergeArea = excelSelectAreaString(info.column, line+1, info.column+info.mergeLen-1, line+1)
            sheet.merge_range(mergeArea, info.title, format)

            info.setTitlePos(line, info.column)

            # print(mergeArea, info.title)
        else:
            sheet.write(line, info.column, info.title, format)
            info.setTitlePos(line, info.column)

            usrInfo.setDict(info.enTitle, info)

        if info.value is not None:
            sheet.write(line+1, info.column, info.value, format)
            info.setValuePos(line+1, info.column)

            if endLine < (line+1):
                endLine = line + 3

        if info.commit is not None:
            sheet.write(line+2, info.column, info.commit, format)
            info.setCommitPos(line+2, info.column)

            if endLine <= (line+4):
                endLine = line + 4

        if endLine < line:
            endLine = line + 2

    # print(startLine, endLine)
    return endLine

def writeSkinReportTitleToExcel(sheet, startLen, usrInfo, format):
    enTitle = skinInfoType.enTitle()
    chTitle = skinInfoType.chTitle()

    mergeArea = excelSelectAreaString(0, startLen+1, len(enTitle)-1, startLen+1)
    sheet.merge_range(mergeArea, u'人体皮肤电阻数据', format)

    for i in range(len(chTitle)):
        colStr = excelColumnString(i)
        temp = str('{0}:{1}'.format(colStr, colStr))

        sheet.set_column(temp, 20)
        sheet.write(startLen+1, i, chTitle[i], format)

        # 写入到字典中，索引用
        info = skinRes.skinUserInfo(chTitle[i], enTitle[i], 0, startLen+2, i)
        info.setTitlePos(startLen+2, i)

        usrInfo.setDict(enTitle[i], info)
        usrInfo.append(info, usrInfo.keywordMax())

    endLine = startLen + 3
    # print(startLen, endLine)
    return endLine

def writeSkinReportDataToExcel(sheet, startLen, srcData, dataLen, format):
    for x in range(dataLen):
        curData = srcData.at(x).toReportData()
        lineStr = str('A{0}'.format(x+startLen))
        # print(lineStr, len(curData), curData, type(curData))

        sheet.write_row(lineStr, curData, format)
    
    endLine = startLen + dataLen + 1
    # print(startLen, endLine)
    return endLine

# 创建酸痛程度比较图 起始列 R 17
# 创建总图  数据图、误差图
# 创建体型检测图表
# 创建数据分区图
# 创建数据比较图
def excelSeriesDataAreaString(sheet, startPart, startLine, endPart, endLine):
    return str('={0}!${1}${2}:${3}${4}'.format(sheet, excelColumnString(startPart), startLine, excelColumnString(endPart), endLine))

def usrInfo2excelPos(usrInfo, type=0, title=None):
    if title is not None:
        return usrInfo.getDictValuePos(title)
    else:
        return usrInfo.type2pos(type)

def usrInfo2excelChartArea(sheetName, usrInfo, type, begin=0, end=0):
    pos = usrInfo2excelPos(usrInfo, type)

    if pos is None:
        return None

    startCol = pos[1]
    startRow = pos[0]
    endCol = pos[3]
    endRow = pos[2]

    if begin:
        startRow = startRow + begin + 1
    else:
        startRow = startRow + 1

    if end:
        endRow = endRow + end
    else:
        endRow = endRow + 1

    partArea = excelSeriesDataAreaString(sheetName, startCol, startRow, endCol, endRow)

    return partArea

def createSorenessCompareChart(chartInfo, row, column):
    # Create a new Chart object.
    chart = chartInfo.workbook.add_chart({'type': 'column'})

    # Configure the chart. In simplest case we add one or more data series.
    partArea  = usrInfo2excelChartArea(chartInfo.sheetName, chartInfo.usrInfo, skinUsrEnum.sorenessTitle.value)
    line1Area = usrInfo2excelChartArea(chartInfo.sheetName, chartInfo.usrInfo, skinUsrEnum.beforeSoreness.value)
    # line2Area = usrInfo2excelChartArea(chartInfo.sheetName, chartInfo.usrInfo, skinUsrEnum.afterSoreness.value)
    line3Area = usrInfo2excelChartArea(chartInfo.sheetName, chartInfo.usrInfo, skinUsrEnum.calSoreness.value)

    # if (partArea is None) and (line1Area is None) and (line2Area is None) and (line3Area is None):
    #     return 

    if (partArea is None) and (line1Area is None) and (line3Area is None):
        return 

    chart.add_series({'name': u'人体主观酸痛程度', 'categories': partArea, 'values': line1Area})
    # chart.add_series({'name': '结束按摩', 'categories': partArea, 'values': line2Area})
    chart.add_series({'name': u'算法计算酸痛程度', 'categories': partArea, 'values': line3Area})

    # Configure the chart axes.
    chart.set_x_axis({'name': u'部位'})
    chart.set_y_axis({'name': u'酸痛程度'})

    # Turn off chart legend. It is on by default in Excel.
    chart.set_legend({'position': 'bottom'})
    chart.set_title ({'name': u'酸痛程度对比图'})
    # chart.set_size({'width': 350, 'height': 200})
    # chart.set_style(10)
    # Insert the chart into the worksheet.
    chartInfo.worksheet.insert_chart(excelTableString(row, column), chart)

def createSingleLineChart(chartInfo, title, row, column, dispInfo, begin, end):
    # Create a chart object.
    chart = chartInfo.workbook.add_chart({'type': 'line'})

    # Configure the series of the chart from the dataframe data.
    if end > chartInfo.usrData.count():
        end = chartInfo.usrData.count()

    timeLineArea  = usrInfo2excelChartArea(chartInfo.sheetName, chartInfo.usrInfo, skinUsrEnum.curTime.value, begin, end)
    valueLineArea = usrInfo2excelChartArea(chartInfo.sheetName, chartInfo.usrInfo, dispInfo, begin, end)

    if (timeLineArea is None) and (valueLineArea is None):
        return 

    chart.add_series({'name': u'原始数据', 'categories': timeLineArea, 'values': valueLineArea,})

    # Configure the chart axes.
    chart.set_x_axis({'name': u'time(s)', 'position_axis': 'on_tick'})
    chart.set_y_axis({'name': u'resistor(Ω)', 'major_gridlines': {'visible': False}})

    # Turn off chart legend. It is on by default in Excel.
    chart.set_legend({'position': 'bottom'})
    chart.set_title ({'name': title})
    chart.set_size({'width': 850, 'height': 600})
    chart.set_style(10)
    # Insert the chart into the worksheet.
    chartInfo.worksheet.insert_chart(excelTableString(row, column), chart)

    return column + 50

def createSegComperaChart(chartInfo, title, row, column, seg, seg1):
    # Create a chart object.
    chart = chartInfo.workbook.add_chart({'type': 'line'})

    # Configure the series of the chart from the dataframe data.

    s1Begin = seg.begin
    s1End   = seg.end + 2
    if s1Begin > chartInfo.usrData.count():
        s1Begin = chartInfo.usrData.count()

    if s1End > chartInfo.usrData.count():
        s1End = chartInfo.usrData.count()

    s2Begin = seg1.begin
    s2End   = seg1.end + 2
    if s2Begin > chartInfo.usrData.count():
        s2Begin = chartInfo.usrData.count()

    if s2End > chartInfo.usrData.count():
        s2End = chartInfo.usrData.count()

    begin = 0
    if s1End <= s2End:
        end = s2End
    else:
        end = s1End

    timeLineArea  = usrInfo2excelChartArea(chartInfo.sheetName, chartInfo.usrInfo, skinUsrEnum.curTime.value, begin, end)
    valueLineArea = usrInfo2excelChartArea(chartInfo.sheetName, chartInfo.usrInfo, skinUsrEnum.resistorValue.value, s1Begin, s1End)
    value1LineArea = usrInfo2excelChartArea(chartInfo.sheetName, chartInfo.usrInfo, skinUsrEnum.resistorValue.value, s2Begin, s2End)

    if (timeLineArea is None) and (valueLineArea is None) and (value1LineArea is None):
        return 

    chart.add_series({'name': seg.getDescString(),  'categories': timeLineArea, 'values': valueLineArea,})
    chart.add_series({'name': seg1.getDescString(), 'categories': timeLineArea, 'values': value1LineArea,})

    # Configure the chart axes.
    chart.set_x_axis({'name': u'time(s)', 'position_axis': 'on_tick'})
    chart.set_y_axis({'name': u'resistor(Ω)', 'major_gridlines': {'visible': False}})

    # Turn off chart legend. It is on by default in Excel.
    chart.set_legend({'position': u'bottom'})
    chart.set_title ({'name': title})
    chart.set_size({'width': 850, 'height': 600})
    chart.set_style(10)
    # Insert the chart into the worksheet.
    chartInfo.worksheet.insert_chart(excelTableString(row, column), chart)

def createSkinPartChart(chartInfo, xPos, yPos):
    for seg in chartInfo.usrData.partData:
        title = seg.getDescString() + u'-折线图'
        createSingleLineChart(chartInfo, title, xPos, yPos, skinUsrEnum.resistorValue.value, seg.begin, seg.end+2)
        xPos += 15

    return yPos + 50

def createSkinSegChart(chartInfo, xPos, yPos):
    initXPos = xPos
    partLen = len(chartInfo.usrData.partData)
    endYPos = yPos
    # 不显示总图
    for i in range(1, partLen):
        part = chartInfo.usrData.partData[i]
        ins = 1

        for seg in part.getSegData():
            title = str(u'{0}.{1}-区间折线图'.format(ins, seg.getDescString()))
            createSingleLineChart(chartInfo, title, xPos, yPos, skinUsrEnum.resistorValue.value, seg.begin, seg.end+2)
            xPos += 15
            ins  += 1

        endYPos = yPos
        xPos = initXPos
        yPos += 40

    return endYPos + 50

def createSkinSegCompareChart(chartInfo, xPos, yPos):
    dataLen = len(chartInfo.usrData.partData)
    initYPos = yPos
    maxYPos = yPos
    graphId = 1

    # 不显示总图
    for i in range(1, dataLen):
        seg = chartInfo.usrData.partData[i]

        for j in range(i+1, dataLen):
            comperaSeg = chartInfo.usrData.partData[j]
            title = str(u'{0}.{1}-{2}-对比图'.format(graphId, seg.getDescString(), comperaSeg.getDescString()))
            createSegComperaChart(chartInfo, title, xPos, yPos, seg, comperaSeg)
            
            if maxYPos < yPos:
                maxYPos = yPos
            
            yPos += 35
            graphId += 1

        xPos += 15
        yPos = initYPos

    return maxYPos + 50

class excelChartInfo:
    def __init__(self, workbook, worksheet, sheetName, usrInfo, usrData):
        self.workbook  = workbook
        self.worksheet = worksheet
        self.sheetName = sheetName
        self.usrInfo   = usrInfo
        self.usrData   = usrData

def exportSkinDataFile(path, usrData):
    data = usrData.allSrcData
    if len(data) == 0:
        return 0

    dataLen = len(data)
    lineLen = len(data[0])
    # print(path, str(dataLen) + 'x' + str(lineLen))

    file = QFile(path)

    if file.open(QFile.ReadWrite | QFile.Truncate) == False:
        return 0

    outText = QTextStream(file)

    for x in range(dataLen):
        lineData = data[x]
        writeString = ''
        for y in range(lineLen):
            writeString += str(lineData[y])
            
            if y != (lineLen-1):
                writeString += ','

        writeString += '\n'
        outText << writeString

    writeString = str('3,6,{0},{1},{2},{3},{4},{5}\n'.format(usrData.sorenessIndex[0],usrData.sorenessIndex[1],usrData.sorenessIndex[2],
                                                             usrData.sorenessIndex[3],usrData.sorenessIndex[4],usrData.sorenessIndex[5]))

    outText << writeString

    file.close()

    return 1

def exportSkinDataReportFile(parent, title, usrInfo, data):
    formatList = 'xlsx Files (*.xlsx)'
    defaultName = str('./{0}.xlsx'.format(title))
    path, ok = QFileDialog.getSaveFileName(parent, '文件保存', defaultName, formatList)

    if ok == '':
        return

    # print(path, str(dataLen) + 'x' + str(lineLen))
    fileBaseName = os.path.splitext(path)[0]

    excel_file = path
    sheetName = 'sheet1'

    srcData = data.allData

    # 创建一个新的文件
    workbook = xlsxwriter.Workbook(excel_file)
    worksheet = workbook.add_worksheet(sheetName)

    # Add a header format.
    header_format = workbook.add_format({'align': 'center', 'valign': 'vcenter', 'border': 1})

    curLine = 0
    curLine = writeSkinReportHeadToExcel(worksheet, curLine, usrInfo, header_format)
    curLine = writeSkinReportTitleToExcel(worksheet, curLine, usrInfo, header_format)

    dataLen = srcData.count()
    if dataLen == 0:
        # 关闭文件
        workbook.close()
        QMessageBox.critical(parent, "错误", excel_file + "文件导出失败!")
        return

    curLine = writeSkinReportDataToExcel(worksheet, curLine, srcData, dataLen, header_format)

    # 创建酸痛程度比较图
    chartInfo = excelChartInfo(workbook, worksheet, sheetName, usrInfo, data)
    createSorenessCompareChart(chartInfo, 17, 3)

    # 创建数据总图
    chartXPos = 17
    chartYPos = 25
    createSingleLineChart(chartInfo, u'数据总图', chartXPos, chartYPos, skinUsrEnum.resistorValue.value, 0, data.count())
    createSingleLineChart(chartInfo, u'误差总图', chartXPos+15, chartYPos, skinUsrEnum.curError.value, 0, data.count())

    # 显示段数据 单段图
    chartXPos = 17
    chartYPos = 60
    chartYPos = createSkinPartChart(chartInfo, chartXPos, chartYPos)
    chartYPos = createSkinSegChart(chartInfo, chartXPos, chartYPos)

    # 创建段数据对比图
    chartYPos = createSkinSegCompareChart(chartInfo, chartXPos, chartYPos)

    file = QFile(excel_file)
    if file.open(QFile.ReadWrite | QFile.Truncate) == False:
        QMessageBox.critical(parent, "错误", fileBaseName +'.csv' + "文件导出失败!")
        return
    else:
        file.close()
        # 关闭文件
        workbook.close()

    if exportSkinDataFile(fileBaseName+'.csv', data) == 0:
        QMessageBox.critical(parent, "错误", fileBaseName+'.csv' + "文件导出失败!")
        return 

    successInfo = excel_file + '文件导出成功!'
    successInfo += '\n'
    successInfo += fileBaseName+'.csv' + '文件导出成功!'
    QMessageBox.information(parent, '成功', successInfo)
